Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Dynamic Extensions

Additional I/O is generated by the extension of segments. Remember that segments are allocated for data in the database at creation time. As the table grows, extents are added to accommodate this growth.

Dynamic extension not only causes additional I/Os, it also causes additional SQL statements to be executed. These additional calls, known as recursive calls, as well as the additional I/Os can impact performance.

You can check the number of recursive calls through the dynamic performance table, V$SYSSTAT. Use the following command:

SQL> SELECT name, value
  2  FROM v$SYSSTAT
  3  WHERE name = 'recursive calls';

NAME                                                                 VALUE
----------------------------------------------------------------  --------
recursive calls                                                       5440

Check for recursive calls after your application has started running and then 15 to 20 minutes later. This information will tell you approximately how many recursive calls the application is causing. Recursive calls are also caused by the following:

  Execution of Data Definition Language statements.
  Execution of SQL statements within stored procedures, functions, packages, and anonymous PL/SQL blocks.
  Enforcement of referential integrity constraints.
  The firing of database triggers.
  Misses on the data dictionary cache.

As you can see, many other conditions can also cause recursive calls. One way to check whether you are creating extents dynamically is to check the table DBA_EXTENTS. If you see that many extents have been created, it may be time to export your data, rebuild the tablespace, and reload the data.

Sizing a segment large enough to fit your data properly benefits you in two ways:

  Blocks in a single extent are contiguous and allow multiblock reads to be more effective, thus reducing I/O.
  Large extents are less likely to be dynamically extended.

Try to size your segments so that dynamic extension is generally avoided and there is adequate space for growth.

PCTFREE and PCTUSED Command Options

Another way to improve performance and decrease overhead is to use the PCTFREE and PCTUSED options of the STORAGE clause in the CREATE CLUSTER, CREATE TABLE, CREATE INDEX, and CREATE SNAPSHOT commands. By using PCTFREE and PCTUSED, you have more exact control over the use of the data blocks themselves. In many cases, knowing your application and your data can help you improve overall system performance.

You use PCTFREE and PCTUSED for several purposes. Both options are of a performance nature and are space related. PCTFREE and PCTUSED can effectively speed up access to data blocks—but at the price of wasting space if you’re not careful. Another important effect of PCTFREE and PCTUSED is to reduce chaining.

Think of PCTFREE as a high water mark and PCTUSED as a low water mark. If the space in a data block is such that there is less space left than PCTFREE, no new rows can be added in that block until the amount of space in the table is less than PCTUSED.

The sum of PCTFREE and PCTUSED cannot exceed 100. Because PCTFREE actually represents a high water mark of 100 – PCTFREE, if the sum of the two exceeds 100, there is an inconsistency in the formula and PCTFREE would be less than PCTUSED.

An Example

Assume that you have the following values for PCTFREE and PCTUSED:

PCTFREE = 20
PCTUSED = 40

In this example, you can add new rows to the data block until the data block becomes 80 percent full (100 percent – 20 percent free). When this occurs, no more rows can be added to this data block; the space is reserved for growth of the existing rows.

You can add new rows to the data block only when the percentage of available space in the block has been reduced to 40 percent (used). This effectively saves space in the data blocks for growth of rows and avoids chaining.

The defaults for PCTFREE and PCTUSED are as follows:

PCTFREE = 10
PCTUSED = 40

PCTFREE

PCTFREE has the effect of reserving space in the data block for growth of existing rows. New rows can be added to the data block until the amount of space remaining in the data block is less than PCTFREE percent.

A high PCTFREE value has the following effects:

  There is a large amount of space for growth of existing rows.
  Improves performance because blocks have to be reorganized less frequently.
  Improves performance because chaining is reduced.
  Typically requires more space because blocks are not used as efficiently. There will always be a moderate amount of empty space in the data blocks.

A lower PCTFREE value has the opposite effects:

  There is less space for growth of existing rows.
  Performance is reduced because reorganization may become more frequent
  Performance is reduced because rows may have to be chained more often, increasing CPU use as well as causing additional I/Os.
  Space is used more effectively. Blocks are filled more completely, thus reducing waste.

Using PCTFREE can help if you have an application that frequently inserts new data into rows. Because the PCTFREE option is used in the CREATE CLUSTER, CREATE TABLE, CREATE INDEX, and CREATE SNAPSHOT commands, it is worth the effort to look at each of your tables, clusters, and indexes individually and decide on an effective PCTFREE value for each.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.